/**
 * CSV数据分析工具 - 输出Excel报告
 * 分析结果可以在WPS/Excel中查看
 */

const fs = require('fs');
const Papa = require('papaparse');

/**
 * 分析CSV并生成Excel格式的CSV报告
 * （使用CSV格式，可以直接用WPS/Excel打开）
 */
function analyzeToExcel(filePath, outputPath) {
    return new Promise((resolve, reject) => {
        console.log(`分析文件: ${filePath}`);
        console.log(`输出报告: ${outputPath}`);
        console.log('-'.repeat(50));

        let headers = [];
        const stats = {
            totalRows: 0,
            columns: [],
            columnTypes: {},
            columnStats: {},
            missingValues: {},
            sampleData: []
        };

        let rowCount = 0;
        const sampleSize = 10000;

        const stream = fs.createReadStream(filePath, { encoding: 'utf-8' });

        Papa.parse(stream, {
            header: true,
            dynamicTyping: false,
            skipEmptyLines: true,
            
            step: function(result, parser) {
                rowCount++;

                if (rowCount === 1 && result.meta.fields) {
                    stats.columns = result.meta.fields;
                    
                    result.meta.fields.forEach(col => {
                        stats.columnStats[col] = {
                            numeric: [],
                            text: [],
                            empty: 0
                        };
                        stats.missingValues[col] = 0;
                        stats.columnTypes[col] = 'unknown';
                    });
                }

                if (rowCount <= 20) {
                    stats.sampleData.push(result.data);
                }

                if (rowCount <= sampleSize) {
                    stats.columns.forEach(col => {
                        const value = result.data[col];

                        if (value === null || value === undefined || value === '') {
                            stats.missingValues[col]++;
                            stats.columnStats[col].empty++;
                        } else {
                            const numValue = parseFloat(value);
                            if (!isNaN(numValue)) {
                                stats.columnStats[col].numeric.push(numValue);
                            } else {
                                stats.columnStats[col].text.push(value);
                            }
                        }
                    });
                } else if (rowCount === sampleSize + 1) {
                    parser.abort();
                }

                if (rowCount % 50000 === 0) {
                    process.stdout.write(`\r已分析: ${rowCount.toLocaleString()} 行`);
                }
            },

            complete: function() {
                if (rowCount < 50000) {
                    stats.totalRows = rowCount;
                    finishAnalysis();
                } else {
                    console.log('\n\n正在统计总行数...');
                    countTotalRows(filePath).then(total => {
                        stats.totalRows = total;
                        finishAnalysis();
                    });
                }

                function finishAnalysis() {
                    stats.columns.forEach(col => {
                        const colStats = stats.columnStats[col];
                        if (colStats.numeric.length > colStats.text.length) {
                            stats.columnTypes[col] = 'numeric';
                        } else if (colStats.text.length > 0) {
                            stats.columnTypes[col] = 'text';
                        }
                    });

                    console.log('\n\n生成Excel报告...');
                    
                    generateExcelReport(stats, filePath, outputPath, sampleSize);
                    
                    console.log(`\n✅ 报告已生成: ${outputPath}`);
                    console.log(`\n双击打开该文件即可在WPS/Excel中查看！`);
                    
                    resolve(stats);
                }
            },

            error: reject
        });
    });
}

function countTotalRows(filePath) {
    return new Promise((resolve) => {
        let count = 0;
        const stream = fs.createReadStream(filePath, { encoding: 'utf-8' });
        
        Papa.parse(stream, {
            header: true,
            step: function() {
                count++;
                if (count % 50000 === 0) {
                    process.stdout.write(`\r已统计: ${count.toLocaleString()} 行`);
                }
            },
            complete: function() {
                console.log('');
                resolve(count);
            }
        });
    });
}

/**
 * 生成Excel格式的CSV报告
 */
function generateExcelReport(stats, filePath, outputPath, sampleSize) {
    const fileSize = (fs.statSync(filePath).size / (1024 * 1024)).toFixed(2);
    
    // 创建报告数据
    const reportData = [];
    
    // 1. 基本信息部分
    reportData.push(['CSV数据分析报告']);
    reportData.push(['']);
    reportData.push(['文件名', filePath]);
    reportData.push(['文件大小', `${fileSize} MB`]);
    reportData.push(['总行数', stats.totalRows.toLocaleString()]);
    reportData.push(['总列数', stats.columns.length]);
    reportData.push(['分析行数', Math.min(stats.totalRows, sampleSize).toLocaleString()]);
    reportData.push(['生成时间', new Date().toLocaleString('zh-CN')]);
    reportData.push(['']);
    reportData.push(['']);
    
    // 2. 列信息详情
    reportData.push(['列信息详情']);
    reportData.push(['序号', '列名', '数据类型', '缺失值数量', '缺失率(%)', '最小值', '最大值', '平均值', '唯一值数量']);
    
    stats.columns.forEach((col, idx) => {
        const type = stats.columnTypes[col];
        const missing = stats.missingValues[col];
        const missingPercent = ((missing / Math.min(stats.totalRows, sampleSize)) * 100).toFixed(2);
        const colStats = stats.columnStats[col];
        
        let min = '', max = '', avg = '', uniqueCount = '';
        
        if (type === 'numeric' && colStats.numeric.length > 0) {
            const numbers = colStats.numeric;
            min = Math.min(...numbers).toFixed(2);
            max = Math.max(...numbers).toFixed(2);
            avg = (numbers.reduce((a, b) => a + b, 0) / numbers.length).toFixed(2);
        } else if (type === 'text') {
            uniqueCount = new Set(colStats.text).size;
        }
        
        reportData.push([
            idx + 1,
            col,
            type === 'numeric' ? '数值' : type === 'text' ? '文本' : '未知',
            missing,
            missingPercent,
            min,
            max,
            avg,
            uniqueCount
        ]);
    });
    
    reportData.push(['']);
    reportData.push(['']);
    
    // 3. 缺失值汇总
    reportData.push(['缺失值汇总']);
    reportData.push(['列名', '缺失值数量', '缺失率(%)']);
    
    const missingEntries = Object.entries(stats.missingValues)
        .filter(([col, count]) => count > 0)
        .sort((a, b) => b[1] - a[1]);
    
    if (missingEntries.length > 0) {
        missingEntries.forEach(([col, count]) => {
            const percent = ((count / Math.min(stats.totalRows, sampleSize)) * 100).toFixed(2);
            reportData.push([col, count, percent]);
        });
    } else {
        reportData.push(['没有缺失值', '', '']);
    }
    
    reportData.push(['']);
    reportData.push(['']);
    
    // 4. 数据预览
    reportData.push(['数据预览（前20行）']);
    reportData.push(stats.columns);
    
    stats.sampleData.forEach(row => {
        reportData.push(stats.columns.map(col => row[col] || ''));
    });
    
    // 写入CSV文件（可以用WPS/Excel打开）
    const csv = Papa.unparse(reportData, {
        encoding: 'utf-8'
    });
    
    // 添加BOM以支持中文
    const BOM = '\uFEFF';
    fs.writeFileSync(outputPath, BOM + csv, 'utf-8');
}

/**
 * 主函数
 */
async function main() {
    const csvFiles = [
        'output/基本设施.csv',
        'output/设施集合.csv'
    ];

    console.log('='.repeat(50));
    console.log('CSV分析工具 - 生成Excel报告');
    console.log('='.repeat(50));

    console.log('\n可用的CSV文件:');
    csvFiles.forEach((file, idx) => {
        try {
            const stats = fs.statSync(file);
            const sizeMB = (stats.size / (1024 * 1024)).toFixed(2);
            console.log(`${idx + 1}. ${file} (${sizeMB} MB)`);
        } catch (e) {
            console.log(`${idx + 1}. ${file} (文件不存在)`);
        }
    });

    const inputFile = csvFiles[0];
    const outputFile = 'output/分析报告.csv';

    console.log(`\n开始分析: ${inputFile}\n`);

    try {
        await analyzeToExcel(inputFile, outputFile);
        
        console.log('\n分析完成！');
        console.log(`\n📄 Excel报告: ${outputFile}`);
        console.log('\n💡 提示: 用WPS表格或Excel打开 ${outputFile} 查看报告');
        
    } catch (error) {
        console.error('分析失败:', error);
    }
}

if (require.main === module) {
    main();
}

module.exports = { analyzeToExcel };

